package Messages;

import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import javax.swing.JOptionPane;

import ocsf.server.ConnectionToClient;
import server.SQLConnection;
/**
 * this class implements the users search books in db
 * @author Ron Shenkerman
 * @author Gabi Revah
 * @author Aviad Almog
 * @author Ivan Onyshchenko
 */
public class MessageSearch {
	private ConnectionToClient client ;
	private String CopyID,Title,Author,PlaceOnShelf,EndDate;
	private int flag=0;
/**
 * constractor of this class 	
 * @param client
 */
	public MessageSearch(ConnectionToClient client) {
		super();
		this.client = client;
	}
/**
 * getting the search results from the DB 
 * @param topic
 * @param searchtext
 * @throws SQLException
 * @throws IOException
 */
	public void SearchResult(String topic, String searchtext) throws SQLException, IOException {
		
		System.out.println("Searching result in DB"+searchtext);
		System.out.println("ooooo"+topic);
		Statement stmt,stmt1;
		SQLConnection conn = new  SQLConnection();
		stmt = conn.getConnection().createStatement();
		stmt1 = conn.getConnection().createStatement();
		switch (topic)	//choose the selected search topic
		{
			case ("Title"):
			{
				flag=0;
				ResultSet rs= stmt.executeQuery("SELECT db1.books.Title,db1.books.Author,db1.books.Placeonshelf,db1.bookscopies.copyId FROM db1.lending,db1.books,db1.bookscopies where db1.books.ID=db1.bookscopies.BID AND ((db1.bookscopies.copyId=db1.lending.copyId AND db1.lending.Lend='no') OR (db1.bookscopies.copyId NOT IN (select db1.lending.copyid from db1.lending))) AND db1.books.Title LIKE '%"+searchtext+"%' GROUP by db1.bookscopies.copyId;");
				if (!rs.next())
				{
					
					ResultSet rs1= stmt.executeQuery("select min(db1.lending.Enddate) as mindate,db1.books.Title,db1.books.Author,db1.bookscopies.copyId from db1.lending,db1.bookscopies,db1.books where db1.books.Id=db1.bookscopies.BID and db1.bookscopies.copyid=db1.lending.copyid and db1.books.title LIKE '%"+searchtext+"%' and db1.lending.lend='yes' and db1.bookscopies.copyid not in (select db1.reservation.bookid from db1.reservation) GROUP by db1.books.Title;");
					ArrayList<String> arr2=new ArrayList<String>();
					arr2.add("Search");
					arr2.add("nofreecopy");
					while (rs1.next())
					{
						flag=1;
						CopyID=rs1.getString(4);	
						Title=rs1.getString(2);
						Author=rs1.getString(3);
						EndDate=rs1.getString(1);
						arr2.add(CopyID);
						arr2.add(Title);
						arr2.add(Author);
						arr2.add(EndDate);
					}
					if (flag==1)
					{
						client.sendToClient(arr2);
					}
					if (!rs1.next() & flag==0)
						{
							ArrayList<String> arr=new ArrayList<String>();
							arr.add("XXsearch");
							client.sendToClient(arr);
							flag=1;
							break;
						}
				}
					
				if (flag==0)
				{
					ArrayList<String> arr=new ArrayList<String>();
					arr.add("Search");
					arr.add("freecopy");
					while (rs.next())
					{
					CopyID=rs.getString(4);	
					Title=rs.getString(1);
					Author=rs.getString(2);
					PlaceOnShelf=rs.getString(3);
					arr.add(CopyID);
					arr.add(Title);
					arr.add(Author);
					arr.add(PlaceOnShelf);
					}
				client.sendToClient(arr);
				}
				break;
			}
			
		
			case ("Subject"):
			{
				flag=0;
				ResultSet rs= stmt.executeQuery("SELECT db1.books.Title,db1.books.Author,db1.books.Placeonshelf,db1.bookscopies.copyId FROM db1.lending,db1.books,db1.bookscopies,db1.bookscategory where db1.books.ID=db1.bookscopies.BID AND ((db1.bookscopies.copyId=db1.lending.copyId AND db1.lending.Lend='no') OR (db1.bookscopies.copyId NOT IN (select db1.lending.copyid from db1.lending))) AND db1.books.ID=db1.bookscategory.BookID AND db1.bookscategory.CategoryName LIKE '%"+searchtext+"%' GROUP by db1.bookscopies.copyId");
				if (!rs.next())
				{
					
					ResultSet rs1= stmt.executeQuery("select min(db1.lending.Enddate) as mindate,db1.books.Title,db1.books.Author,db1.bookscopies.copyId from db1.lending,db1.bookscopies,db1.books,db1.bookscategory where db1.books.Id=db1.bookscopies.BID and db1.bookscopies.copyid=db1.lending.copyid and db1.books.Id=db1.bookscategory.bookid and db1.bookscategory.categoryname LIKE '%"+searchtext+"%' and db1.lending.lend='yes' group by db1.lending.copyid");
					ArrayList<String> arr2=new ArrayList<String>();
					arr2.add("Search");
					arr2.add("nofreecopy");
					while (!rs1.next())
					{
						
						
							
							ArrayList<String> arr=new ArrayList<String>();
							arr.add("XXsearch");
							client.sendToClient(arr);
							flag=1;
							break;
						
					}
						if (flag==0) { 
						CopyID=rs1.getString(4);	
						Title=rs1.getString(2);
						Author=rs1.getString(3);
						EndDate=rs1.getString(1);
						arr2.add(CopyID);
						arr2.add(Title);
						arr2.add(Author);
						arr2.add(EndDate);
						}
					
					if (flag==0)
						{
						client.sendToClient(arr2);
						}
					
				}
				if (flag==0)
					{
					ArrayList<String> arr=new ArrayList<String>();
					arr.add("Search");
					arr.add("freecopy");
					while (rs.next())
					{
					CopyID=rs.getString(4);	
					Title=rs.getString(1);
					Author=rs.getString(2);
					PlaceOnShelf=rs.getString(3);
					arr.add(CopyID);
					arr.add(Title);
					arr.add(Author);
					arr.add(PlaceOnShelf);
					}
					client.sendToClient(arr);
					}
				break;
			}
			case ("Author"):
			{
				flag=0;
				ResultSet rs= stmt.executeQuery("SELECT db1.books.Title,db1.books.Author,db1.books.Placeonshelf,db1.bookscopies.copyId FROM db1.lending,db1.books,db1.bookscopies where db1.books.ID=db1.bookscopies.BID AND ((db1.bookscopies.copyId=db1.lending.copyId AND db1.lending.Lend='no') OR (db1.bookscopies.copyId NOT IN (select db1.lending.copyid from db1.lending))) AND db1.books.Author LIKE '%"+searchtext+"%' GROUP by db1.bookscopies.copyId;");
				if (!rs.next())
				{
					
					ResultSet rs1= stmt.executeQuery("select min(db1.lending.Enddate) as mindate,db1.books.Title,db1.books.Author,db1.bookscopies.copyId from db1.lending,db1.bookscopies,db1.books where db1.books.Id=db1.bookscopies.BID and db1.bookscopies.copyid=db1.lending.copyid and db1.books.Author LIKE '%"+searchtext+"%' and db1.lending.lend='yes'");
					ArrayList<String> arr2=new ArrayList<String>();
					arr2.add("Search");
					arr2.add("nofreecopy");
					while (rs1.next())
					{
						if (rs1.getString(1)==null)
						{
							
							ArrayList<String> arr=new ArrayList<String>();
							arr.add("XXsearch");
							client.sendToClient(arr);
							flag=1;
							break;
						}
						if (flag==0) 
						{ 
					
						CopyID=rs1.getString(4);	
						Title=rs1.getString(2);
						Author=rs1.getString(3);
						EndDate=rs1.getString(1);
						arr2.add(CopyID);
						arr2.add(Title);
						arr2.add(Author);
						arr2.add(EndDate);
						}
					}
					if (flag==0)
						{
						client.sendToClient(arr2);
						}
					
				}
				if (flag==0)
				{
					ArrayList<String> arr=new ArrayList<String>();
					arr.add("Search");
					arr.add("freecopy");
					while (rs.next())
					{
					CopyID=rs.getString(4);	
					Title=rs.getString(1);
					Author=rs.getString(2);
					PlaceOnShelf=rs.getString(3);
					arr.add(CopyID);
					arr.add(Title);
					arr.add(Author);
					arr.add(PlaceOnShelf);
					}
				client.sendToClient(arr);
				}
				break;
			}
			case ("Free Text"):
			{
				System.out.println("Cant search Free Text for now" );
				break;

			}
			case (""):
				System.out.println("Error" );
			break;
		}

		
	}

}
